package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.ICourseDao;
import cn.edu.klmy.models.course.Course;
import cn.edu.klmy.models.course.CourseStructure;

@Repository
public class CourseDao extends JdbcDaoSupport implements ICourseDao {

    private static final String ADD_COURSE = "INSERT INTO COURSE(COURSE_NAME, COURSE_CODE, STUDY_CREDIT, COURSE_EVALUATION, COURSE_CONTENT, COURSE_REQUIREMENT, COURSE_NUMBER, COURSE_PERIOD, COURSE_COMMENT, COURSE_STRUCTURE_ID) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    @Override
    public void addCourse(Course course) {
        getJdbcTemplate().update(ADD_COURSE, new Object[]{
                course.getCourseName(), course.getCourseCode(), course.getStudyCredit(), course.getCourseEvaluation(),
                course.getCourseContent(), course.getCourseRequirement(), course.getCourseNumber(),
                course.getCoursePeriod(), course.getCourseComment(), course.getCourseStructure() == null ? null : course.getCourseStructure().getId()
        });
    }

    private static final String UPDATE_COURSE = "UPDATE COURSE SET COURSE_NAME = ?, COURSE_CODE = ?, STUDY_CREDIT = ?, COURSE_EVALUATION = ?, COURSE_CONTENT = ?, COURSE_REQUIREMENT = ?, COURSE_NUMBER = ?, COURSE_PERIOD = ?, COURSE_COMMENT = ?, COURSE_STRUCTURE_ID = ? WHERE ID = ?";

    @Override
    public void updateCourse(Course course) {
        getJdbcTemplate().update(UPDATE_COURSE, new Object[]{
                course.getCourseName(), course.getCourseCode(), course.getStudyCredit(), course.getCourseEvaluation(),
                course.getCourseContent(), course.getCourseRequirement(), course.getCourseNumber(),
                course.getCoursePeriod(), course.getCourseComment(), course.getCourseStructure() == null ? null : course.getCourseStructure().getId(), course.getId()
        });
    }
    
    private static final String COURSE_EXISTS = "SELECT COUNT(*) FROM COURSE WHERE COURSE_NAME = ?";
    @Override
    public boolean courseExists(String courseName) {
        int result = getJdbcTemplate().queryForInt(COURSE_EXISTS, new Object[]{courseName});

        if (result < 1) {
            return false;
        }
        return true;
    }

    private static final String GET_COURSES_S = "SELECT A.ID, A.COURSE_NAME, A.COURSE_CODE, A.STUDY_CREDIT, A.COURSE_EVALUATION, A.COURSE_CONTENT, A.COURSE_REQUIREMENT, A.COURSE_COMMENT, A.COURSE_NUMBER, A.COURSE_PERIOD, B.COURSE_STRUCTURE_ID, B.STUDY_AREA, B.STUDY_SUBJECT, B.STUDY_TYPE, A.STUDY_CREDIT FROM COURSE A, COURSE_STRUCTURE B WHERE B.COURSE_STRUCTURE_ID = A.COURSE_STRUCTURE_ID AND ";
    @Override
    public List<Course> getCourses(String fieldName, Object value) {
        String sql = GET_COURSES_S + fieldName + "=" + value;
        return (List<Course>) getJdbcTemplate().query(sql, new CourseMapper());
    }

    private static final String REMOVE_COURSE = "DELETE FROM COURSE WHERE ";
    private static final String REMOVE_TEXTBOOK = "DELETE FROM TEXTBOOK WHERE COURSE_ID IN (SELECT ID FROM COURSE WHERE ";

    @Override
    public void removeCourse(String fieldName, Object value) {
        String sql1 = REMOVE_COURSE + fieldName + "=" + value;
        String sql2 = REMOVE_TEXTBOOK + fieldName + "=" + value + ")";
        getJdbcTemplate().update(sql2);
        getJdbcTemplate().update(sql1);
    }

    private static final String GET_COURSES = "SELECT A.ID, A.COURSE_NAME, A.COURSE_CODE, A.STUDY_CREDIT, A.COURSE_EVALUATION, A.COURSE_CONTENT, A.COURSE_REQUIREMENT, A.COURSE_COMMENT, A.COURSE_NUMBER, A.COURSE_PERIOD, B.COURSE_STRUCTURE_ID, B.STUDY_AREA, B.STUDY_SUBJECT, B.STUDY_TYPE, A.STUDY_CREDIT FROM COURSE A, COURSE_STRUCTURE B";

    @Override
    public List<Course> getCourses(Map<String, Object[]> filter, int pageNum, int pageSize) {
        StringBuffer sql = new StringBuffer(GET_COURSES);
        DaoUtil.arristSql(sql, filter);
        return (List<Course>) getJdbcTemplate().query(sql.toString(), new Object[]{}, new CourseMapper());
    }

    @Override
    public int getCourseCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("COURSE", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    //顾伟添加
    public List<Course> getAllCourses() {
        String sql = "SELECT * FROM COURSE";
        return (List<Course>) getJdbcTemplate().query(sql, new CourseRowMapper());
    }

    public String getCourseName(int courseId) {
        String sql = "SELECT COURSE_NAME FROM COURSE WHERE ID=?";
        return getJdbcTemplate().queryForObject(sql, String.class, new Object[]{courseId});
    }

    //顾伟添加
    private class CourseRowMapper implements RowMapper<Course> {

        @Override
        public Course mapRow(ResultSet resultSet, int i) throws SQLException {
            Course course = new Course();
            course.setId(resultSet.getInt("ID"));
            course.setCourseName(resultSet.getString("COURSE_NAME"));
            course.setCourseCode(resultSet.getString("COURSE_CODE"));
            course.setCourseEvaluation(resultSet.getString("COURSE_EVALUATION"));
            course.setCourseContent(resultSet.getString("COURSE_CONTENT"));
            course.setCourseRequirement(resultSet.getString("COURSE_REQUIREMENT"));
            course.setCourseComment(resultSet.getString("COURSE_COMMENT"));
            course.setCourseNumber(resultSet.getString("COURSE_NUMBER"));
            course.setCoursePeriod(resultSet.getString("COURSE_PERIOD"));
            course.setStudyCredit(resultSet.getInt("STUDY_CREDIT"));
            return course;
        }
    }

    private static class CourseMapper implements RowMapper<Course> {
        @Override
        public Course mapRow(ResultSet resultSet, int i) throws SQLException {
            Course course = new Course();
            course.setId(resultSet.getInt("ID"));
            course.setCourseName(resultSet.getString("COURSE_NAME"));
            course.setCourseCode(resultSet.getString("COURSE_CODE"));
            course.setCourseEvaluation(resultSet.getString("COURSE_EVALUATION"));
            course.setCourseContent(resultSet.getString("COURSE_CONTENT"));
            course.setCourseRequirement(resultSet.getString("COURSE_REQUIREMENT"));
            course.setCourseComment(resultSet.getString("COURSE_COMMENT"));
            course.setCourseNumber(resultSet.getString("COURSE_NUMBER"));
            course.setCoursePeriod(resultSet.getString("COURSE_PERIOD"));
            course.setStudyCredit(resultSet.getInt("STUDY_CREDIT"));
            if (null != resultSet.getString("COURSE_STRUCTURE_ID")) {
                CourseStructure courseStructure = new CourseStructure();
                courseStructure.setId(resultSet.getInt("COURSE_STRUCTURE_ID"));
                courseStructure.setStudyArea(resultSet.getString("STUDY_AREA"));
                courseStructure.setStudySubject(resultSet.getString("STUDY_SUBJECT"));
                courseStructure.setStudyType(resultSet.getInt("STUDY_TYPE"));
                course.setCourseStructure(courseStructure);
            }
            return course;
        }
    }
}
